import hashlib
import json

from flask import Flask, request
from sqlalchemy import create_engine, Integer, Column, String, Table, select, Select, or_, func
from sqlalchemy.orm import sessionmaker, scoped_session, declarative_base

app = Flask(__name__)

# 创建一个引擎，目的是连接到我们的数据库上
engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/mumushouji',echo=True)
# 打开数据库的连接会话
session = sessionmaker(engine)
# 保证线程安全
db_session = scoped_session(session)
# 获取基类
Base = declarative_base()


class User(Base):
    # 表结构的反射加载
    __table__ = Table('user', Base.metadata, autoload_with=engine)


# session.execute(select(User).filter_by(name="sandy")).scalar_one()

r = db_session.query(User).filter_by(user_id=1).first()
print(r.username)
sql = Select(User).filter_by(user_id=1)
r = db_session.execute(sql).scalar_one()
print(r.username)


# 或
result = db_session.query(User).filter(or_(User.user_id == 1, User.user_id == 2)).all()
# limit
result = db_session.query(User).limit(4).all()
result = db_session.query(User).limit(4).offset(2).all()
# order by
result = db_session.query(User).order_by(User.user_id.desc()).all()
# like
result = db_session.query(User).filter(User.nickname.like('%子%')).all()

for r in result:
    print(r.username, r.nickname)


result = db_session.query(func.sum(User.user_id)).first()
print(result)


if __name__ == '__main__':
    app.run(debug=True)
